﻿INSERT dbo.Permission
        ( Name, Type, Route )
VALUES  ( '停车场管理', -- Name - varchar(50)
          1, -- Type - int
          '/Admin/Parking'  -- Route - varchar(100)
          )
INSERT dbo.Permission
        ( Name, Type, Route )
VALUES  ( '停车场管理_查询', -- Name - varchar(50)
          5, -- Type - int
          '/Admin/ParkingQuery'  -- Route - varchar(100)
          )
INSERT dbo.Permission
        ( Name, Type, Route )
VALUES  ( '停车场管理_保存', -- Name - varchar(50)
          4, -- Type - int
          '/Admin/SaveParking'  -- Route - varchar(100)
          )
INSERT dbo.Permission
        ( Name, Type, Route )
VALUES  ( '停车场管理_删除', -- Name - varchar(50)
         3, -- Type - int
          '/Admin/DeleteParking'  -- Route - varchar(100)
          )
INSERT dbo.Permission
        ( Name, Type, Route )
VALUES  ( '停车场管理_扩展配置', -- Name - varchar(50)
         1, -- Type - int
          '/Admin/ParkingConfig'  -- Route - varchar(100)
          )
INSERT dbo.Permission
        ( Name, Type, Route )
VALUES  ( '停车场管理_扩展配置保存', -- Name - varchar(50)
         1, -- Type - int
          '/Admin/SaveParkingConfig'  -- Route - varchar(100)
          )
UPDATE dbo.Role SET PermissionID='1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32' WHERE ID=1


USE [Parking]
GO
/****** Object:  StoredProcedure [dbo].[P_ORDER_Report]    Script Date: 09/19/2018 20:16:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Nick.Wang
-- Create date: 20180917
-- Description:	支付报表
-- =============================================
ALTER PROCEDURE [dbo].[P_ORDER_Report]
 @beginTime DATETIME,
  @endTime DATETIME,
 @enID INT
AS
BEGIN
DECLARE @t TABLE(
order_id VARCHAR(50),--订单号
Wxuser_name VARCHAR(50),--支付人名称
car_no VARCHAR(50),--车牌
pay_fee DECIMAL(8,2),--实付金额
UnionPay_fee DECIMAL(8,2),--云闪付
Weixin_fee DECIMAL(8,2),--微信支付
Alipay_fee DECIMAL(8,2),--支付宝
Tonglian_fee DECIMAL(8,2),--通联支付
 XJPay_fee DECIMAL(8,2),--现金支付
  Enterprise_Name VARCHAR(50),
  CCEnterprise_Name VARCHAR(50),
 pay_time DATETIME, --支付时间
 create_time DATETIME
);
IF(@enID >0)
BEGIN
	SELECT o.order_id,o.Wxuser_name,o.car_no,o.pay_fee,o.pay_type,o.pay_time,e1.create_time,e1.Enterprise_Name,e2.Enterprise_Name AS CCEnterprise_Name INTO #temp FROM dbo.order_info o
LEFT JOIN dbo.Enterprise e1 ON o.EnterpriseID=e1.ID
LEFT JOIN dbo.Enterprise e2 ON o.CCEnterpriseID=e2.ID WHERE o.pay_status=3 AND e2.ID=@enID AND o.pay_time>=@beginTime AND o.pay_time<=@endTime

---云闪付
INSERT INTO @t( order_id ,Wxuser_name ,car_no ,pay_fee ,UnionPay_fee ,Weixin_fee ,Alipay_fee , Tonglian_fee ,XJPay_fee , Enterprise_Name , CCEnterprise_Name ,pay_time ,create_time)
SELECT order_id,Wxuser_name,car_no,pay_fee,pay_fee,0,0,0,0,Enterprise_Name,CCEnterprise_Name,pay_time,create_time FROM #temp WHERE pay_type=5 
---微信支付
INSERT INTO @t( order_id ,Wxuser_name ,car_no ,pay_fee ,UnionPay_fee ,Weixin_fee ,Alipay_fee , Tonglian_fee ,XJPay_fee , Enterprise_Name , CCEnterprise_Name ,pay_time ,create_time)
SELECT order_id,Wxuser_name,car_no,pay_fee,0,pay_fee,0,0,0,Enterprise_Name,CCEnterprise_Name,pay_time,create_time FROM #temp WHERE pay_type=6 
---支付宝
INSERT INTO @t( order_id ,Wxuser_name ,car_no ,pay_fee ,UnionPay_fee ,Weixin_fee ,Alipay_fee , Tonglian_fee ,XJPay_fee , Enterprise_Name , CCEnterprise_Name ,pay_time ,create_time)
SELECT order_id,Wxuser_name,car_no,pay_fee,0,0,pay_fee,0,0,Enterprise_Name,CCEnterprise_Name,pay_time,create_time FROM #temp WHERE pay_type=7
---通联支付
INSERT INTO @t( order_id ,Wxuser_name ,car_no ,pay_fee ,UnionPay_fee ,Weixin_fee ,Alipay_fee , Tonglian_fee ,XJPay_fee , Enterprise_Name , CCEnterprise_Name ,pay_time ,create_time)
SELECT order_id,Wxuser_name,car_no,pay_fee,0,0,0,pay_fee,0,Enterprise_Name,CCEnterprise_Name,pay_time,create_time FROM #temp WHERE pay_type=8
---现金支付
INSERT INTO @t( order_id ,Wxuser_name ,car_no ,pay_fee ,UnionPay_fee ,Weixin_fee ,Alipay_fee , Tonglian_fee ,XJPay_fee , Enterprise_Name , CCEnterprise_Name ,pay_time ,create_time)
SELECT order_id,Wxuser_name,car_no,pay_fee,0,0,0,0,pay_fee,Enterprise_Name,CCEnterprise_Name,pay_time,create_time FROM #temp WHERE pay_type=9
SELECT * FROM @t ORDER BY create_time 
DROP TABLE #temp;
END
ELSE
BEGIN
	SELECT o.order_id,o.Wxuser_name,o.car_no,o.pay_fee,o.pay_type,o.pay_time,e1.create_time,e1.Enterprise_Name,e2.Enterprise_Name AS CCEnterprise_Name INTO #temp_2  FROM dbo.order_info o
LEFT JOIN dbo.Enterprise e1 ON o.EnterpriseID=e1.ID
LEFT JOIN dbo.Enterprise e2 ON o.CCEnterpriseID=e2.ID WHERE o.pay_status=3 AND o.pay_time>=@beginTime AND o.pay_time<=@endTime

---云闪付
INSERT INTO @t( order_id ,Wxuser_name ,car_no ,pay_fee ,UnionPay_fee ,Weixin_fee ,Alipay_fee , Tonglian_fee ,XJPay_fee , Enterprise_Name , CCEnterprise_Name ,pay_time ,create_time)
SELECT order_id,Wxuser_name,car_no,pay_fee,pay_fee,0,0,0,0,Enterprise_Name,CCEnterprise_Name,pay_time,create_time FROM #temp_2 WHERE pay_type=5 
---微信支付
INSERT INTO @t( order_id ,Wxuser_name ,car_no ,pay_fee ,UnionPay_fee ,Weixin_fee ,Alipay_fee , Tonglian_fee ,XJPay_fee , Enterprise_Name , CCEnterprise_Name ,pay_time ,create_time)
SELECT order_id,Wxuser_name,car_no,pay_fee,0,pay_fee,0,0,0,Enterprise_Name,CCEnterprise_Name,pay_time,create_time FROM #temp_2 WHERE pay_type=6 
---支付宝
INSERT INTO @t( order_id ,Wxuser_name ,car_no ,pay_fee ,UnionPay_fee ,Weixin_fee ,Alipay_fee , Tonglian_fee ,XJPay_fee , Enterprise_Name , CCEnterprise_Name ,pay_time ,create_time)
SELECT order_id,Wxuser_name,car_no,pay_fee,0,0,pay_fee,0,0,Enterprise_Name,CCEnterprise_Name,pay_time,create_time FROM #temp_2 WHERE pay_type=7
---通联支付
INSERT INTO @t( order_id ,Wxuser_name ,car_no ,pay_fee ,UnionPay_fee ,Weixin_fee ,Alipay_fee , Tonglian_fee ,XJPay_fee , Enterprise_Name , CCEnterprise_Name ,pay_time ,create_time)
SELECT order_id,Wxuser_name,car_no,pay_fee,0,0,0,pay_fee,0,Enterprise_Name,CCEnterprise_Name,pay_time,create_time FROM #temp_2 WHERE pay_type=8
---现金支付
INSERT INTO @t( order_id ,Wxuser_name ,car_no ,pay_fee ,UnionPay_fee ,Weixin_fee ,Alipay_fee , Tonglian_fee ,XJPay_fee , Enterprise_Name , CCEnterprise_Name ,pay_time ,create_time)
SELECT order_id,Wxuser_name,car_no,pay_fee,0,0,0,0,pay_fee,Enterprise_Name,CCEnterprise_Name,pay_time,create_time FROM #temp_2 WHERE pay_type=9
SELECT * FROM @t ORDER BY create_time 
DROP TABLE #temp_2;
END


END
